﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using Model;

namespace BO
{
    /// <summary>
    /// Summary description for VYC17BO
    /// </summary>
    public class VYC17BO:DBConnectionBO
    {
        public VYC17BO():base()
        {           
        }
        public List<ModelVYC17> GetAllList()
        {
            var list = new List<ModelVYC17>();
            const string query = "SELECT [Id],[UserName],[Password],[PasswordHash],[IdPhanQuyen],[AnhDaiDien],[HoTen],[NgayCapNhat],[TrangThai] FROM [VYC-17]";
            var sqlDataAdapter = new SqlDataAdapter(query, connection);
            var dataTable = new DataTable();
            sqlDataAdapter.Fill(dataTable); // fill data to Datatable
            CloseConnection(); // Close connection to database
            for (var i = 0; i < dataTable.Rows.Count; i++)
            {
                var model = new ModelVYC17();

                model.Id = (dataTable.Rows[i][0] == DBNull.Value) ? 0 : Convert.ToInt32(dataTable.Rows[i][0]);
                model.UserName = (dataTable.Rows[i][1] == DBNull.Value) ? string.Empty : dataTable.Rows[i][1].ToString();
                model.Password = (dataTable.Rows[i][2] == DBNull.Value) ? string.Empty : dataTable.Rows[i][2].ToString();
                model.PasswordHash = (dataTable.Rows[i][3] == DBNull.Value) ? string.Empty : dataTable.Rows[i][3].ToString();
                model.IdPhanQuyen = (dataTable.Rows[i][4] == DBNull.Value) ? 0 : Convert.ToInt32(dataTable.Rows[i][4]);
                model.AnhDaiDien = (dataTable.Rows[i][5] == DBNull.Value) ? string.Empty : dataTable.Rows[i][5].ToString();
                model.HoTen = (dataTable.Rows[i][6] == DBNull.Value) ? string.Empty : dataTable.Rows[i][6].ToString();
                model.NgayCapNhat = (dataTable.Rows[i][7] == DBNull.Value) ? DateTime.Now : (DateTime)dataTable.Rows[i][7];
                model.TrangThai = (dataTable.Rows[i][8] == DBNull.Value) ? 0 : Convert.ToInt32(dataTable.Rows[i][8]);

                list.Add(model);
            }
            return list;
        }

        /// <summary>
        /// Add new a model in data
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int AddData(ModelVYC17 model)
        {
            var query = "INSERT INTO [VYC-17]([UserName],[Password],[PasswordHash],[IdPhanQuyen],[AnhDaiDien],[HoTen],[NgayCapNhat],[TrangThai]) VALUES (" +
                        "'" + model.UserName + "', '" +model.Password + "', '" + model.PasswordHash + "', " +model.IdPhanQuyen + ", '"
                        + model.AnhDaiDien + "', N'" + model.HoTen + "', "
                        + "GETDATE(), " + model.TrangThai + ")";
            var sqlCommand = new SqlCommand(query, connection);
            try
            {
                var status = sqlCommand.ExecuteNonQuery();
                CloseConnection(); // close connection
                return status;
            }
            catch (Exception)
            {
                CloseConnection();
                return 0;
                throw;
            }
        }

        /// <summary>
        /// Edit a model in data
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int EditData(ModelVYC17 model)
        {
            var query = "UPDATE [VYC-17] SET " +
                        "[UserName] = '" + model.UserName + "', " +
                        "[Password]= '" + model.Password + "', " +
                        "[PasswordHash]= '" + model.PasswordHash + "', " +
                        "[IdPhanQuyen] = " + model.IdPhanQuyen + ", " + 
                        "[AnhDaiDien] = '" + model.AnhDaiDien + "', " +
                        "[HoTen] = N'" + model.HoTen + "', " +
                        "NgayCapNhat = GETDATE(), TrangThai = " + model.TrangThai +
                        " WHERE Id = " + model.Id;
            var sqlCommand = new SqlCommand(query, connection);
            try
            {
                var status = sqlCommand.ExecuteNonQuery();
                CloseConnection(); // close connection
                return status;
            }
            catch (Exception)
            {
                CloseConnection();
                return 0;
                throw;
            }
        }

        /// <summary>
        /// Delete a model in data
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int DeleteData(ModelVYC17 model)
        {
            var query = "DELETE FROM [VYC-17] " +
                        " WHERE Id = " + model.Id;
            var sqlCommand = new SqlCommand(query, connection);
            try
            {
                var status = sqlCommand.ExecuteNonQuery();
                CloseConnection(); // close connection
                return status;
            }
            catch (Exception)
            {
                CloseConnection();
                return 0;
                throw;
            }
        }

        /// <summary>
        /// Get by id
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public List<ModelVYC17> GetById(int id)
        {
            var list = new List<ModelVYC17>();
            var query = "SELECT [Id],[UserName],[Password],[PasswordHash],[IdPhanQuyen],[AnhDaiDien],[HoTen],[NgayCapNhat],[TrangThai] FROM [VYC-17] WHERE Id = " + id;
            var sqlDataAdapter = new SqlDataAdapter(query, connection);
            var dataTable = new DataTable();
            sqlDataAdapter.Fill(dataTable); // fill data to Datatable
            CloseConnection(); // Close connection to database
            for (var i = 0; i < dataTable.Rows.Count; i++)
            {
                var model = new ModelVYC17();

                model.Id = (dataTable.Rows[i][0] == DBNull.Value) ? 0 : Convert.ToInt32(dataTable.Rows[i][0]);
                model.UserName = (dataTable.Rows[i][1] == DBNull.Value) ? string.Empty : dataTable.Rows[i][1].ToString();
                model.Password = (dataTable.Rows[i][2] == DBNull.Value) ? string.Empty : dataTable.Rows[i][2].ToString();
                model.PasswordHash = (dataTable.Rows[i][3] == DBNull.Value) ? string.Empty : dataTable.Rows[i][3].ToString();
                model.IdPhanQuyen = (dataTable.Rows[i][4] == DBNull.Value) ? 0 : Convert.ToInt32(dataTable.Rows[i][4]);
                model.AnhDaiDien = (dataTable.Rows[i][5] == DBNull.Value) ? string.Empty : dataTable.Rows[i][5].ToString();
                model.HoTen = (dataTable.Rows[i][6] == DBNull.Value) ? string.Empty : dataTable.Rows[i][6].ToString();
                model.NgayCapNhat = (dataTable.Rows[i][7] == DBNull.Value) ? DateTime.Now : (DateTime)dataTable.Rows[i][7];
                model.TrangThai = (dataTable.Rows[i][8] == DBNull.Value) ? 0 : Convert.ToInt32(dataTable.Rows[i][8]);

                list.Add(model);
            }
            return list;
        }

        /// <summary>
        /// Check login
        /// </summary>
        /// <param name="info"></param>
        /// <returns></returns>
        public List<ModelVYC17> CheckLogin(ModelVYC17 info)
        {
            var list = new List<ModelVYC17>();
            var query = "SELECT [Id],[UserName],[Password],[PasswordHash],[IdPhanQuyen],[AnhDaiDien],[HoTen],[NgayCapNhat],[TrangThai] FROM [VYC-17] WHERE UserName like '" + info.UserName + "' and PasswordHash like '" + info.PasswordHash + "'";
            var sqlDataAdapter = new SqlDataAdapter(query, connection);
            var dataTable = new DataTable();
            sqlDataAdapter.Fill(dataTable); // fill data to Datatable
            CloseConnection(); // Close connection to database
            for (var i = 0; i < dataTable.Rows.Count; i++)
            {
                var model = new ModelVYC17();

                model.Id = (dataTable.Rows[i][0] == DBNull.Value) ? 0 : Convert.ToInt32(dataTable.Rows[i][0]);
                model.UserName = (dataTable.Rows[i][1] == DBNull.Value) ? string.Empty : dataTable.Rows[i][1].ToString();
                model.Password = (dataTable.Rows[i][2] == DBNull.Value) ? string.Empty : dataTable.Rows[i][2].ToString();
                model.PasswordHash = (dataTable.Rows[i][3] == DBNull.Value) ? string.Empty : dataTable.Rows[i][3].ToString();
                model.IdPhanQuyen = (dataTable.Rows[i][4] == DBNull.Value) ? 0 : Convert.ToInt32(dataTable.Rows[i][4]);
                model.AnhDaiDien = (dataTable.Rows[i][5] == DBNull.Value) ? string.Empty : dataTable.Rows[i][5].ToString();
                model.HoTen = (dataTable.Rows[i][6] == DBNull.Value) ? string.Empty : dataTable.Rows[i][6].ToString();
                model.NgayCapNhat = (dataTable.Rows[i][7] == DBNull.Value) ? DateTime.Now : (DateTime)dataTable.Rows[i][7];
                model.TrangThai = (dataTable.Rows[i][8] == DBNull.Value) ? 0 : Convert.ToInt32(dataTable.Rows[i][8]);

                list.Add(model);
            }
            return list;
        }

        public List<ModelVYC17> GetByUsername(string username)
        {
            var list = new List<ModelVYC17>();
            var query = "SELECT [Id],[UserName],[Password],[PasswordHash],[IdPhanQuyen],[AnhDaiDien],[HoTen],[NgayCapNhat],[TrangThai] FROM [VYC-17] WHERE UserName like @username";
            var sqlCommand = new SqlCommand(query, connection);
            sqlCommand.Parameters.Add("@username", SqlDbType.VarChar).Value = username;
            var reader = sqlCommand.ExecuteReader();

            while (reader.Read())
            {
                var model = new ModelVYC17();

                model.Id = Convert.ToInt32(reader["Id"]);
                model.UserName = reader["UserName"].ToString();
                model.Password = reader["Password"].ToString();
                model.PasswordHash = reader["PasswordHash"].ToString();
                model.IdPhanQuyen = Convert.ToInt32(reader["IdPhanQuyen"]);
                model.AnhDaiDien = reader["AnhDaiDien"].ToString();
                model.HoTen = reader["HoTen"].ToString();                
                model.NgayCapNhat = (DateTime)reader["NgayCapNhat"];
                model.TrangThai = Convert.ToInt32(reader["TrangThai"]);

                list.Add(model);
            }
            return list;
        }

    }
}